﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OpenSISDataConnection;
using OSISStudentSetupDataLayer;
using OSISViewFormLayoutBusinessLayer;

/// <summary>
/// Summary description for ViewFormLayoutDL
/// </summary>
/// 
namespace OSISViewFormLayoutDataLayer
{
    public class ViewFormLayoutDL
    {
        public ViewFormLayoutDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public System.Data.DataTable getfieldsbycategory(ViewFormLayoutBL SchoolSpCat)
        {
            DataTable dtdefault = null;
            DataTable dtcustom = null;
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_ViewFormLayout_Order where Sch_Id=" + SchoolSpCat.SchoolId + " and Cat_Id=" + SchoolSpCat.CategoryId + "").Tables[0];
            if (dt.Rows.Count == 0)
            {


                dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status  where School_Id =" + SchoolSpCat.SchoolId + ")as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id WHERE SSDF.Cat_Id=" + SchoolSpCat.CategoryId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View = 'V_E')").Tables[0];
                dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom from Student_Setup_Custom_Field  SSCF where Category_Id=" + SchoolSpCat.CategoryId + " and sch_id=" + SchoolSpCat.SchoolId + " and SSCF.Status!='I' and Field_View='V_E'").Tables[0];

                dtdefault.Merge(dtcustom);
                
                return dtdefault;

            }
            else
            {
                dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status  where School_Id =" + SchoolSpCat.SchoolId + ")as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_ViewFormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + SchoolSpCat.SchoolId + " WHERE SSDF.Cat_Id=" + SchoolSpCat.CategoryId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View = 'V_E') ORDER BY SSFO.Sort_Order").Tables[0];

                dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSFO.* from Student_Setup_Custom_Field  SSCF left outer join Student_Setup_ViewFormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id AND SSCF.Category_Id=SSFO.Cat_Id and SSCF.sch_id=SSFO.Sch_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + SchoolSpCat.CategoryId + " and SSCF.sch_id=" + SchoolSpCat.SchoolId + " and SSCF.Status!='I' and Field_View='V_E' order by SSFO.Sort_Order").Tables[0];
                dtdefault.Merge(dtcustom);
                DataView myDataView = dtdefault.DefaultView;
                myDataView.Sort = "Sort_Order";

                return myDataView.ToTable();

                


            }
        }
        //public System.Data.DataTable getfieldsbycategory(ViewFormLayoutBL SchoolSpCat)
        //{
        //    DataTable dtdefault = null;
        //    DataTable dtcustom = null;


        //    dtdefault = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status  where School_Id =" + SchoolSpCat.SchoolId + ")as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id WHERE SSDF.Cat_Id=" + SchoolSpCat.CategoryId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL)").Tables[0];



        //    dtcustom = DataConnection.ConnectionPath.Getdata("select SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom from Student_Setup_Custom_Field  SSCF where Category_Id=" + SchoolSpCat.CategoryId + " and sch_id=" + SchoolSpCat.SchoolId + " and SSCF.Status!='I'").Tables[0];
        //    dtdefault.Merge(dtcustom);

        //    return dtdefault;

        //}


        public System.Data.DataTable getaddressfieldsbyfieldid(ViewFormLayoutBL AddressFields)
        {
            DataTable dtdefault = null;
            // DataTable dtcustom = null;


            dtdefault = DataConnection.ConnectionPath.Getdata("select SSAF.Add_Id as FieldId,SSAF.Field_name as FieldName,SSAF.Field_Type as FieldType,SSADFS.Status as Status,SSAF.Status as IsMandatory,SSDF.DefaultField as Default_Custom from Student_Setup_Address_Field SSAF left outer join(select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + AddressFields.SchoolId + ")as SSADFS on SSAF.Add_Id=SSADFS.Add_Id left outer join(select * from Student_Setup_Default_Field where Def_Field_Id=" + AddressFields.FieldId + ") SSDF on SSAF.Def_Field_Id=SSDF.Def_Field_Id WHERE SSAF.Def_Field_Id=" + AddressFields.FieldId + " and (SSADFS.Status!='I' or SSADFS.Status IS NULL) and (SSADFS.Field_View='V_E')").Tables[0];


            //object contactidlist = "";
            //contactidlist = DataConnection.ConnectionPath.Getdata("select Cont_Id_List from Student_Setup_Additional_Contact_List where Sch_Id=" + AddressFields.SchoolId + " and Def_Field_Id=" + AddressFields.FieldId + "").Tables[0].Rows[0][0];



            //dtcustom = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId, SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory from Student_Setup_Additional_Default_Contact_Field SSADCF  left outer join (select * from Student_Setup_Additional_Contact_Status where Sch_Id=" + AddressFields.SchoolId + ") as SSACS on SSADCF.Cont_Id=SSACS.Cont_Id where SSADCF.Cont_Id in (" + contactidlist.ToString() + ") ").Tables[0];
            //dtdefault.Merge(dtcustom);

            return dtdefault;

        }


        public System.Data.DataTable getadditionalcontactfields(ViewFormLayoutBL AddressFields)
        {

            DataTable dtcustom = null;


            object contactidlist = "";
            contactidlist = DataConnection.ConnectionPath.Getdata("select Cont_Id_List from Student_Setup_Additional_Contact_List where Sch_Id=" + AddressFields.SchoolId + " and Def_Field_Id=" + AddressFields.FieldId + "").Tables[0].Rows[0][0];



            dtcustom = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId, SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory,SSCF.CustomField as Default_Custom from Student_Setup_Additional_Default_Contact_Field SSADCF  left outer join (select * from Student_Setup_Additional_Contact_Status where Sch_Id=" + AddressFields.SchoolId + ") as SSACS on SSADCF.Cont_Id=SSACS.Cont_Id left outer join Student_Setup_Additional_Contact_List SSACL on SSADCF.Cont_Id in (" + contactidlist.ToString() + ") left outer join (select * from Student_Setup_Custom_Field where extrafield_id=" + AddressFields.FieldId + ") SSCF on SSACL.Def_Field_Id=SSCF.extrafield_id where SSADCF.Cont_Id in (" + contactidlist.ToString() + ")  and (SSACS.Status!='I' or SSACS.Status IS NULL)").Tables[0];


            return dtcustom;

        }

        public DataSet Populatedynamiccontrols(ViewFormLayoutBL student)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from Custom_Field_List_Item where control_id=" + student.FieldId + "");
            return ds;
        }
        public DataSet populategender()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from gender");
            return ds;
        }
        public DataSet PopulateEthnicity()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from ethnicity");
            return ds;
        }
        public DataSet PopulateLanguage()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from language");
            return ds;
        }
        public DataSet PopulateState()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("SELECT * FROM look_up_data WHERE TYPE=2");
            return ds;
        }
        public DataSet PopulateCountry()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("SELECT * FROM look_up_data WHERE TYPE=1");
            return ds;
        }
        public DataSet populatrenrollmentcdn()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from enrollment_codes");
            return ds;
        }




        public System.Data.DataTable getsummaryfields(ViewFormLayoutBL SummaryFields)
        {

            DataTable dtcustom = null;



            dtcustom = DataConnection.ConnectionPath.Getdata("select Def_Field_Id as FieldId,(case when Def_Field_Id =13 then 'DOB | Age' else field_name end) as FieldName,Field_Type as FieldType,Status,Cat_Id from Student_Setup_Default_Field where Def_Field_Id in (1,13,110,11,15) order by case Def_Field_Id when 1 then 1 when 13 then 2  when 110 then 3 when 11 then 4 when 15 then 5 end").Tables[0];
           


            return dtcustom;

        }

    }
}
